#!/usr/bin/env node

/**
 * 用户Excel导入调试工具
 * 帮助用户调试他们的Excel文件导入问题
 */

const fs = require('fs');
const path = require('path');
const XLSX = require('xlsx');
const axios = require('axios');
const FormData = require('form-data');

class UserExcelDebugger {
  constructor() {
    this.baseDir = '/Users/andylau/Dropbox/AppDevelopment/CMB250716';
    this.token = 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJ1c2VySWQiOjEsImVtYWlsIjoiYWRtaW5AZXhhbXBsZS5jb20iLCJtZW1iZXJzaGlwTGV2ZWwiOiJhZG1pbiIsImlhdCI6MTc1NzE2NDAyMCwiZXhwIjoxNzU3NzY4ODIwLCJpc3MiOiJjdS1tci1idWxsLWFwaSJ9.2pqn01o6rqlOOoOGaEPXNtwIoK-A86kPty3KWnyvY0Q';
  }

  log(message, type = 'info') {
    const timestamp = new Date().toISOString();
    const prefix = {
      'info': '🔍',
      'success': '✅',
      'error': '❌',
      'fix': '🔧',
      'warning': '⚠️'
    }[type] || 'ℹ️';
    console.log(`${prefix} [${timestamp}] ${message}`);
  }

  findUserExcelFiles() {
    this.log('查找用户的Excel文件');
    
    const possiblePaths = [
      path.join(this.baseDir, '*.xlsx'),
      path.join(this.baseDir, 'uploads', '*.xlsx'),
      path.join(this.baseDir, 'backend', 'uploads', '*.xlsx'),
      path.join(this.baseDir, 'admin', '*.xlsx'),
      '/Users/andylau/Desktop/*.xlsx',
      '/Users/andylau/Downloads/*.xlsx'
    ];
    
    const excelFiles = [];
    
    // 检查当前目录的Excel文件
    try {
      const files = fs.readdirSync(this.baseDir);
      files.forEach(file => {
        if (file.endsWith('.xlsx') && !file.startsWith('test_') && !file.startsWith('real_')) {
          const filePath = path.join(this.baseDir, file);
          const stats = fs.statSync(filePath);
          excelFiles.push({
            name: file,
            path: filePath,
            size: stats.size,
            modified: stats.mtime
          });
        }
      });
    } catch (error) {
      this.log('检查当前目录失败: ' + error.message, 'warning');
    }
    
    // 检查uploads目录
    const uploadsDir = path.join(this.baseDir, 'backend', 'uploads');
    if (fs.existsSync(uploadsDir)) {
      try {
        const files = fs.readdirSync(uploadsDir);
        files.forEach(file => {
          if (file.endsWith('.xlsx')) {
            const filePath = path.join(uploadsDir, file);
            const stats = fs.statSync(filePath);
            excelFiles.push({
              name: file,
              path: filePath,
              size: stats.size,
              modified: stats.mtime
            });
          }
        });
      } catch (error) {
        this.log('检查uploads目录失败: ' + error.message, 'warning');
      }
    }
    
    if (excelFiles.length > 0) {
      this.log(`找到 ${excelFiles.length} 个Excel文件:`, 'success');
      excelFiles.forEach((file, index) => {
        this.log(`${index + 1}. ${file.name} (${Math.round(file.size/1024)}KB, 修改时间: ${file.modified.toLocaleString()})`);
      });
    } else {
      this.log('没有找到用户的Excel文件', 'warning');
    }
    
    return excelFiles;
  }

  analyzeExcelFile(filePath) {
    this.log(`分析Excel文件: ${path.basename(filePath)}`);
    
    try {
      const workbook = XLSX.readFile(filePath);
      const sheetNames = workbook.SheetNames;
      
      this.log(`Excel包含 ${sheetNames.length} 个工作表: ${sheetNames.join(', ')}`);
      
      const analysis = {
        fileName: path.basename(filePath),
        sheets: {},
        totalRows: 0,
        hasRequiredSheets: false
      };
      
      // 检查是否有必需的工作表
      const requiredSheets = ['Dishes', 'Variants', 'Options'];
      const hasAllRequired = requiredSheets.every(sheet => sheetNames.includes(sheet));
      analysis.hasRequiredSheets = hasAllRequired;
      
      if (!hasAllRequired) {
        this.log('缺少必需的工作表。需要: ' + requiredSheets.join(', '), 'warning');
        this.log('实际工作表: ' + sheetNames.join(', '), 'warning');
      }
      
      // 分析每个工作表
      sheetNames.forEach(sheetName => {
        const worksheet = workbook.Sheets[sheetName];
        const data = XLSX.utils.sheet_to_json(worksheet, { header: 1 });
        
        const nonEmptyRows = data.filter(row => row.some(cell => cell !== undefined && cell !== ''));
        const headers = nonEmptyRows[0] || [];
        const dataRows = nonEmptyRows.slice(1);
        
        analysis.sheets[sheetName] = {
          headers: headers,
          rowCount: dataRows.length,
          sampleData: dataRows.slice(0, 3) // 前3行数据作为样本
        };
        
        analysis.totalRows += dataRows.length;
        
        this.log(`工作表 "${sheetName}": ${headers.length} 列, ${dataRows.length} 行数据`);
        this.log(`  表头: ${headers.slice(0, 5).join(', ')}${headers.length > 5 ? '...' : ''}`);
        
        if (dataRows.length > 0) {
          const firstRow = dataRows[0];
          this.log(`  第一行数据: ${firstRow.slice(0, 3).join(', ')}${firstRow.length > 3 ? '...' : ''}`);
        }
      });
      
      return analysis;
    } catch (error) {
      this.log('分析Excel文件失败: ' + error.message, 'error');
      return null;
    }
  }

  async testImportProcess(filePath) {
    this.log('测试导入流程');
    
    try {
      // 1. 验证
      const form = new FormData();
      form.append('file', fs.createReadStream(filePath));
      
      this.log('发送验证请求...');
      const validateResponse = await axios.post('http://localhost:3001/api/admin/menu-import/v2/validate', form, {
        headers: {
          ...form.getHeaders(),
          'Authorization': `Bearer ${this.token}`
        },
        timeout: 60000
      });
      
      this.log('验证响应: ' + JSON.stringify(validateResponse.data));
      
      const jobId = validateResponse.data.job_id;
      if (!jobId) {
        throw new Error('未获取到任务ID');
      }
      
      // 2. 等待验证完成
      this.log('等待验证完成...');
      let attempts = 0;
      let jobStatus = 'validating';
      
      while (jobStatus === 'validating' && attempts < 30) {
        await new Promise(resolve => setTimeout(resolve, 1000));
        
        const statusResponse = await axios.get(`http://localhost:3001/api/admin/menu-import/v2/jobs/${jobId}`, {
          headers: { 'Authorization': `Bearer ${this.token}` }
        });
        
        jobStatus = statusResponse.data.status;
        this.log(`第${attempts + 1}次检查，状态: ${jobStatus}`);
        
        if (jobStatus === 'validated') {
          this.log('验证成功！', 'success');
          break;
        } else if (jobStatus === 'failed') {
          this.log('验证失败！', 'error');
          this.log('错误信息: ' + JSON.stringify(statusResponse.data), 'error');
          return false;
        }
        
        attempts++;
      }
      
      if (jobStatus !== 'validated') {
        throw new Error('验证超时或失败');
      }
      
      // 3. 提交
      this.log('开始提交...');
      const commitResponse = await axios.post(`http://localhost:3001/api/admin/menu-import/v2/commit?job_id=${jobId}`, {}, {
        headers: {
          'Authorization': `Bearer ${this.token}`,
          'Content-Type': 'application/json'
        },
        timeout: 60000
      });
      
      this.log('提交响应: ' + JSON.stringify(commitResponse.data));
      
      if (commitResponse.data.success) {
        this.log('提交成功！', 'success');
        return true;
      } else {
        this.log('提交失败: ' + commitResponse.data.message, 'error');
        return false;
      }
      
    } catch (error) {
      this.log('导入测试失败: ' + error.message, 'error');
      if (error.response && error.response.data) {
        this.log('错误详情: ' + JSON.stringify(error.response.data), 'error');
      }
      return false;
    }
  }

  async checkDatabaseAfterImport() {
    this.log('检查导入后的数据库状态');
    
    try {
      const response = await axios.get('http://localhost:3001/api/menu/public/items', {
        headers: { 'Authorization': `Bearer ${this.token}` }
      });
      
      const items = response.data.data.items;
      this.log(`数据库中共有 ${items.length} 条数据`);
      
      // 显示所有数据
      items.forEach((item, index) => {
        this.log(`${index + 1}. ${item.name} - ${item.description || '无描述'}`);
      });
      
      return items;
    } catch (error) {
      this.log('检查数据库失败: ' + error.message, 'error');
      return [];
    }
  }

  async run() {
    console.log('\n' + '='.repeat(80));
    console.log('🔧 用户Excel导入调试工具');
    console.log('🎯 帮助您找出Excel导入问题的根本原因');
    console.log('='.repeat(80) + '\n');
    
    try {
      // 1. 查找用户的Excel文件
      const excelFiles = this.findUserExcelFiles();
      
      if (excelFiles.length === 0) {
        console.log('\n' + '='.repeat(80));
        console.log('❌ 没有找到您的Excel文件');
        console.log('💡 请将您的Excel文件放在以下位置之一:');
        console.log('   - 项目根目录: /Users/andylau/Dropbox/AppDevelopment/CMB250716/');
        console.log('   - 桌面: /Users/andylau/Desktop/');
        console.log('   - 下载文件夹: /Users/andylau/Downloads/');
        console.log('='.repeat(80) + '\n');
        return;
      }
      
      // 2. 分析最新的Excel文件
      const latestFile = excelFiles.sort((a, b) => b.modified - a.modified)[0];
      this.log(`选择最新的Excel文件: ${latestFile.name}`, 'success');
      
      const analysis = this.analyzeExcelFile(latestFile.path);
      if (!analysis) {
        throw new Error('Excel文件分析失败');
      }
      
      // 3. 测试导入流程
      this.log('\n开始测试导入流程...');
      const importSuccess = await this.testImportProcess(latestFile.path);
      
      // 4. 检查数据库
      this.log('\n检查导入结果...');
      const dbItems = await this.checkDatabaseAfterImport();
      
      // 5. 生成报告
      console.log('\n' + '='.repeat(80));
      console.log('📊 调试报告');
      console.log('='.repeat(80));
      console.log(`Excel文件: ${analysis.fileName}`);
      console.log(`工作表数量: ${Object.keys(analysis.sheets).length}`);
      console.log(`总数据行数: ${analysis.totalRows}`);
      console.log(`格式检查: ${analysis.hasRequiredSheets ? '✅ 通过' : '❌ 失败'}`);
      console.log(`导入测试: ${importSuccess ? '✅ 成功' : '❌ 失败'}`);
      console.log(`数据库记录: ${dbItems.length} 条`);
      
      if (!analysis.hasRequiredSheets) {
        console.log('\n❌ 问题诊断: Excel格式不正确');
        console.log('💡 您的Excel文件需要包含以下工作表:');
        console.log('   - Dishes (菜品信息)');
        console.log('   - Variants (规格变体)');
        console.log('   - Options (选项配置)');
      } else if (!importSuccess) {
        console.log('\n❌ 问题诊断: 导入流程失败');
        console.log('💡 请检查上面的错误信息');
      } else {
        console.log('\n✅ 导入成功！');
        console.log('💡 如果前端界面没有显示新数据，请强制刷新浏览器');
      }
      
      console.log('='.repeat(80) + '\n');
      
    } catch (error) {
      console.log('\n' + '='.repeat(80));
      console.log('❌ 调试工具运行失败');
      console.log('错误:', error.message);
      console.log('='.repeat(80) + '\n');
    }
  }
}

// 立即运行
if (require.main === module) {
  const excelDebugger = new UserExcelDebugger();
  excelDebugger.run().catch(console.error);
}

module.exports = UserExcelDebugger;